package com.ttl.stock.db;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.List;

import hibernate.util.HibernateUtil;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;

import com.ttl.bc.ap.invoice.bean.BCAPInvoiceBean;
import com.ttl.common.db.ADAO;
import com.ttl.view.ITTL_MonthlyInventory;
import com.ttl.view.ITTL_OutStock;
import com.ttl.view.VMStockInOut;

public class StockDAO extends ADAO {

	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean getMaterialOutStockReportOld(String sDate, String eDate, String timeToRequestReport, String shelfCode) {

		Session session = null;
		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
			
//			String hql = "DELETE FROM ITTL_OutStock";
//			Query query = session.createQuery(hql);
//			int row = query.executeUpdate();
//			if (row == 0){
//				System.out.println("ITTL_OutStock: Doesn' t deleted any row!");
//			} else{
//				System.out.println("ITTL_OutStock: Deleted Row: " + row);
//			}			
			
//			String[] a = sDate.split("-");
//			hql = "DELETE FROM ITTL_MonthlyInventory " +
//					" WHERE month(inventoryDate)='"+ Integer.parseInt(a[1]) +"' " +
//						  " and year(inventoryDate)='"+ Integer.parseInt(a[0]) +"' " +
//						  " and remark is null ";
			
//			query = session.createQuery(hql);
//			row = query.executeUpdate();
//			if (row == 0){
//				System.out.println("ITTL_MonthlyInventory: Doesn' t deleted any row!");
//			} else{
//				System.out.println("ITTL_MonthlyInventory: Deleted Row: " + row);
//			}			
			
			
			String hql = " SELECT itemCode, sum(qty) " +
				  " FROM VMStockInOut " +
				  " WHERE docDate < '"+sDate+"' "+
				  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
				  "			itemCode like '3-%' or itemCode like '4-%' or " +
				  "			itemCode like '5-%' or itemCode like '6-%' or "+
//				  "  		itemCode like '7-%' ) and whCode ='10'  "+
				  "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+				  
				  " GROUP BY itemCode "+
				  " ORDER BY itemCode ";
			System.out.println(hql);
			List sumItemList = session.createQuery(hql).list();		 	
			System.out.println("sumItemList :"+sumItemList.size());
			
			hql = " FROM VMStockInOut WHERE docDate between '"+sDate+"' and '"+eDate +"' " +
			  	  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
			      "			itemCode like '3-%' or itemCode like '4-%' or " +
			      "			itemCode like '5-%' or itemCode like '6-%' or "+
//			      "  		itemCode like '7-%' ) "+
			      "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+			
				  " order by itemCode, docDate, qty desc";
			System.out.println(">>> "+hql);
			List<VMStockInOut> result = session.createQuery(hql).list();
			System.out.println("result :"+result.size());	
//			int a=0;
//			for (VMStockInOut v: result) {
//				System.out.print("a = "+(a++));
//				System.out.print("\t\t"+v.getDocNo());
//				System.out.println("\t\t"+v.getItemCode());
//			}
			
			hql = " FROM ITTL_MonthlyInventory WHERE remark is null and inventoryDate='"+eDate+"' ";
			List<ITTL_MonthlyInventory> invResult = session.createQuery(hql).list();
			int row=0;
			for (ITTL_MonthlyInventory m : invResult) { 
				session.delete(m);
				row++;
			}
			System.out.println("delete ITTL_MonthlyInventory : "+ row+ " record");

			// ############# old : start			
//			String[] startDate 	  = sDate.split("-");
//			String hql2 = "FROM ITTL_MonthlyInventory WHERE  " ;
//							
//			if(Integer.parseInt(startDate[1])==1) {
//				hql2 +=	" month(inventoryDate)='12' and year(inventoryDate)='"+(Integer.parseInt(startDate[0])-1)+"' ";
//			}else {
//				hql2 +=	" month(inventoryDate)='"+(Integer.parseInt(startDate[1])-1)+"' " +
//						" and year(inventoryDate)='"+(Integer.parseInt(startDate[0]))+"' ";
//			}
//			System.out.println(hql2);
//			//list itemCode ของเดือนยอดยกมา
//			List<ITTL_MonthlyInventory> lastMonthItemList = session.createQuery(hql2).list();			
			// ############# old : end
			
			// ############# new : start
			String[] startDate 	  = sDate.split("-");
			String hql2 = "FROM ITTL_MonthlyInventoryReport WHERE  " ;
							
			if(Integer.parseInt(startDate[1])==1) {
				hql2 +=	" monthReport='12' and yearReport='"+(Integer.parseInt(startDate[0])-1)+"' ";
			}else {
				hql2 +=	" monthReport='"+(Integer.parseInt(startDate[1])-1)+"' " +
						" and yearReport='"+(Integer.parseInt(startDate[0]))+"' ";
			}
			System.out.println(hql2);
			//list itemCode ของเดือนยอดยกมา
			List<ITTL_MonthlyInventoryReport> lastMonthItemList = session.createQuery(hql2).list();				
			
			// ############# new : end
			
			for (int j = 0; j < sumItemList.size(); j++) {
				
				Object[] tuple = (Object[])sumItemList.get(j);
				
				double qtyStock = ((Double)tuple[1]).doubleValue();
				double amountStock = 0d;
				boolean lastQty = false;
//				System.out.print((j+1)+"."+tuple[0]);
//				System.out.print("     result.size() = "+result.size());
//				System.out.println("   qtyStock = "+qtyStock);
				if(result!=null && result.size()==0 && qtyStock!=0) {

					if(lastQty==false) {
						//เพิ่ม record สำหรับ ยอดยกมา
						ITTL_OutStock s = new ITTL_OutStock();
						s.setTimeToRequestReport(timeToRequestReport);							
						s.setItemCode(((String)tuple[0]).toUpperCase());
						s.setDescription("ยอดยกมา");
						s.setStock(qtyStock);
						//start หาราคาย้อนหลังเดือนที่แล้ว		
//						String[] startDate 	  = sDate.split("-");
//						
//						String hql2 = "FROM ITTL_MonthlyInventory " +
//										" WHERE itemCode='"+s.getItemCode()+"' " ;
//										
//						if(Integer.parseInt(startDate[1])==1) {
//							hql2 +=	" and month(inventoryDate)='12' and year(inventoryDate)='"+(Integer.parseInt(startDate[0])-1)+"' ";
//						}else {
//							hql2 +=	" and month(inventoryDate)='"+(Integer.parseInt(startDate[1])-1)+"' " +
//									" and year(inventoryDate)='"+(Integer.parseInt(startDate[0]))+"' ";
//						}
//						
//						List itemList = session.createQuery(hql2).list();
//						ITTL_MonthlyInventory tupleAmount = (ITTL_MonthlyInventory)itemList.get(0);
//						
//						s.setStockAmount( tupleAmount.getAmount());
						//for (ITTL_MonthlyInventory lm : lastMonthItemList) {
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {	
							if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								s.setStockAmount( Double.valueOf(lm.getBalanceAmount()));
								break;
							}
						}
						
						
						amountStock = s.getStockAmount();
						
						if(s.getStock()!=0) {
							s.setPrice(s.getStockAmount()/s.getStock());
						}else{
							s.setPrice(0d);
							s.setStockAmount(0d);
						}
						
						//end หาราคาย้อนหลังเดือนที่แล้ว	
						session.save(s);
						//end เพิ่ม record สำหรับ ยอดยกมา
						lastQty = true;
					}
										
				}else {
				
//					if(j==96) 
//						System.out.println("j = 96");
//					System.out.println("result = "+result.size());
					for (int i = 0; i < result.size(); i++) {
//						System.out.print("i befor="+i);
						ITTL_OutStock os = new StockBO().getITTL_OutStock((VMStockInOut)result.get(i));
						
						os.setTimeToRequestReport(timeToRequestReport);
//						System.out.print("\t\t i after="+i);
//						System.out.print("\t\t tuple[0]="+tuple[0]);
//						System.out.println("\t\t os.getItemCode() = "+os.getItemCode());
						
						if(tuple[0].toString().equalsIgnoreCase(os.getItemCode())) {
//							System.out.print("i ="+i);
//							System.out.print("\t\t"+os.getDocNo());
//							System.out.println("\t\t"+os.getItemCode());
							if(lastQty==false) {
								//เพิ่ม record สำหรับ ยอดยกมา
								ITTL_OutStock s = new ITTL_OutStock();
								s.setTimeToRequestReport(timeToRequestReport);							
								s.setItemCode(((String)tuple[0]).toUpperCase());
								s.setDescription("ยอดยกมา");
								s.setStock(qtyStock);
//								System.out.print("\t\t\t qtyStock="+qtyStock);
								//start หาราคาย้อนหลังเดือนที่แล้ว		
//								String[] startDate 	  = sDate.split("-");
//								
//								String hql2 = "FROM ITTL_MonthlyInventory " +
//												" WHERE itemCode='"+s.getItemCode()+"' " ;
//												
//								if(Integer.parseInt(startDate[1])==1) {
//									hql2 +=	" and month(inventoryDate)='12' and year(inventoryDate)='"+(Integer.parseInt(startDate[0])-1)+"' ";
//								}else {
//									hql2 +=	" and month(inventoryDate)='"+(Integer.parseInt(startDate[1])-1)+"' " +
//											" and year(inventoryDate)='"+(Integer.parseInt(startDate[0]))+"' ";
//								}
////								System.out.println(hql2);
//								List itemList = session.createQuery(hql2).list();
////								System.out.println("itemList.size()="+itemList.size());
//								ITTL_MonthlyInventory tupleAmount = (ITTL_MonthlyInventory)itemList.get(0);
								
//								s.setStockAmount( tupleAmount.getAmount());
								for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
									if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
										if(lm.getBalanceAmount()==null) {
											s.setStockAmount(0d);
										}else {
											s.setStockAmount( Double.valueOf(lm.getBalanceAmount()));	
										}
										
										break;
									}
								}
								if(s.getStockAmount()!=null ) {
									amountStock = s.getStockAmount();
								}else {
									amountStock = 0;
								}
								
								
								if(s.getStock()!=null && s.getStock()!=0) {
//									s.setPrice(s.getStockAmount()/s.getStock());
									s.setPrice(amountStock/s.getStock());
								}else{
									s.setPrice(0d);
									s.setStockAmount(0d);
								}
//								System.out.print("\t yes2\t");
								//end หาราคาย้อนหลังเดือนที่แล้ว	
								session.save(s);
								//end เพิ่ม record สำหรับ ยอดยกมา
								lastQty = true;
							}
//							System.out.print(tuple[0]+"\n");
							if(os.getQtyIN()!=null && os.getQtyIN().doubleValue()>0) {
								qtyStock = qtyStock+os.getQtyIN().doubleValue();
								
								os.setStock(qtyStock);
//								System.out.println("os.getAmountIN() = "+os.getAmountIN());
								amountStock = amountStock+os.getAmountIN();
								
								os.setStockAmount(amountStock);
							}else {
								double price = 0d;
								if(qtyStock>0) {
									java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
									price = new Double(dfm.format(amountStock/qtyStock)).doubleValue();
									
									//price = (amountStock/qtyStock);
								}else {
									price = 0d;
								}
								
								os.setPrice(price);
								
								
								qtyStock = qtyStock-os.getQtyOUT().doubleValue();
								os.setStock(qtyStock);
								if(qtyStock !=0) { 
									os.setAmountOUT(price*os.getQtyOUT().doubleValue());
									amountStock = amountStock-os.getAmountOUT().doubleValue();
								}else {
									os.setAmountOUT(amountStock);
									amountStock = 0;
								}
								os.setStockAmount(amountStock);
								
							}
							//System.out.println(os.getQtyIN().doubleValue()>=0);
							//if(os.getQtyIN().doubleValue()==0) {
								session.save(os);
							//}
						}
					}
					 
					 
				}
				 
				if(shelfCode.equals("10")) {
					 
					
					ITTL_MonthlyInventory mi = new ITTL_MonthlyInventory();
					 
					mi.setItemCode(tuple[0].toString());
					
					if (amountStock>0) {
						mi.setAmount(amountStock);
					}else {					
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
							if(mi.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								if(lm.getBalanceAmount()==null) {
									lm.setBalanceAmount(0d);
								}
								mi.setAmount( lm.getBalanceAmount().doubleValue());
								break;
							}
						}	
					}	
					
					 
					
					String[] endDate 	  = eDate.split("-");
					Date d = new Date(Integer.parseInt(endDate[0])-1900, Integer.parseInt(endDate[1])-1, Integer.parseInt(endDate[2]));
					mi.setInventoryDate(d);
					 
					
					mi.setMonthly(""+Integer.parseInt(endDate[1]));
					 
					//System.out.println(""+Integer.parseInt(endDate[1]));
					mi.setYearly(endDate[0]);
					 
					session.save(mi);
				}
				//System.out.println("j = "+j);
				
			}		
			session.getTransaction().commit();
			System.out.println("it's ok");
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(e.getStackTrace().toString());
			return false;
		}finally {
			session.close();
		}
	}		
	public boolean getMaterialOutStockReport(String sDate, String eDate, String timeToRequestReport, String shelfCode) {

		Session session = null;
		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
						
			String hql = " SELECT itemCode, sum(qty) " +
				  " FROM VMStockInOut " +
				  " WHERE docDate < '"+sDate+"' "+
				  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
				  "			itemCode like '3-%' or itemCode like '4-%' or " +
				  "			itemCode like '5-%' or itemCode like '6-%' or "+
				  "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+				  
				  " GROUP BY itemCode "+
				  " ORDER BY itemCode ";
			System.out.println(hql);
			List sumItemList = session.createQuery(hql).list();		 	
			System.out.println("sumItemList :"+sumItemList.size());
			
			hql = " FROM VMStockInOut WHERE docDate between '"+sDate+"' and '"+eDate +"' " +
			  	  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
			      "			itemCode like '3-%' or itemCode like '4-%' or " +
			      "			itemCode like '5-%' or itemCode like '6-%' or "+
 			      "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+			
				  " order by itemCode, docDate, qty desc";
			System.out.println(">>> "+hql);
			List<VMStockInOut> result = session.createQuery(hql).list();
			System.out.println("result :"+result.size());	
			
			hql = " FROM ITTL_MonthlyInventory WHERE remark is null and inventoryDate='"+eDate+"' ";
			List<ITTL_MonthlyInventory> invResult = session.createQuery(hql).list();
			int row=0;
			for (ITTL_MonthlyInventory m : invResult) { 
				session.delete(m);
				row++;
			}
			System.out.println("delete ITTL_MonthlyInventory : "+ row+ " record");
	
			// ############# new : start
			String[] startDate 	  = sDate.split("-");
			String hql2 = "FROM ITTL_MonthlyInventoryReport WHERE " ;
			//hql2 = hql2 + " itemCode='7-000-G-003' and 	 ";
			
			if(Integer.parseInt(startDate[1])==1) {
				hql2 +=	" monthReport='12' and yearReport='"+(Integer.parseInt(startDate[0])-1)+"' ";
			}else {
				hql2 +=	" monthReport='"+(Integer.parseInt(startDate[1])-1)+"' " +
						" and yearReport='"+(Integer.parseInt(startDate[0]))+"' ";
			}
			hql2 += " order by itemCode ";
			System.out.println(hql2);
			//list itemCode ของเดือนยอดยกมา
			List<ITTL_MonthlyInventoryReport> lastMonthItemList = session.createQuery(hql2).list();				
			
			// ############# new : end
			
			for (int j = 0; j < lastMonthItemList.size(); j++) {
				
				//Object[] tuple = (Object[])sumItemList.get(j);
				ITTL_MonthlyInventoryReport tuple = (ITTL_MonthlyInventoryReport)lastMonthItemList.get(j);
				
				
				//double qtyStock = ((Double)tuple[1]).doubleValue();
				double qtyStock = tuple.getBalanceQty();
				double amountStock = 0d;
				boolean lastQty = false;
 
				if(result!=null && result.size()==0 && qtyStock!=0) {

					if(lastQty==false) {
						//เพิ่ม record สำหรับ ยอดยกมา
						ITTL_OutStock s = new ITTL_OutStock();
						s.setTimeToRequestReport(timeToRequestReport);							
						//s.setItemCode(((String)tuple[0]).toUpperCase());
						s.setItemCode(tuple.getItemCode().toUpperCase());
						s.setDescription("ยอดยกมา");
						s.setStock(qtyStock);
						//start หาราคาย้อนหลังเดือนที่แล้ว		
 
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {	
							if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								//s.setStockAmount( Double.valueOf(lm.getBalanceAmount()));
								BigDecimal c = new BigDecimal(lm.getBalanceAmount()).setScale(4, RoundingMode.HALF_UP);
								s.setStockAmount(c.doubleValue());
								break;
							}
						}
												
						//amountStock = s.getStockAmount();
						amountStock = new BigDecimal(s.getStockAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue();
						
						if(s.getStock()!=0) {
							//s.setPrice(s.getStockAmount()/s.getStock());
							//s.setPrice(new BigDecimal(s.getStockAmount()/s.getStock()).setScale(4, RoundingMode.HALF_UP).doubleValue());
							
							BigDecimal a = new BigDecimal(""+s.getStockAmount());
							BigDecimal b = new BigDecimal(""+s.getStock());
							
							BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
							s.setPrice(c.doubleValue());
						}else{
							s.setPrice(0d);
							s.setStockAmount(0d);
						}
						
						//end หาราคาย้อนหลังเดือนที่แล้ว	
						session.save(s);
						//end เพิ่ม record สำหรับ ยอดยกมา
						lastQty = true;
					}
										
				}else {
				
					for (int i = 0; i < result.size(); i++) {
 						ITTL_OutStock os = new StockBO().getITTL_OutStock((VMStockInOut)result.get(i));
						
						os.setTimeToRequestReport(timeToRequestReport);
						
						//if(tuple[0].toString().equalsIgnoreCase(os.getItemCode())) {
						if(tuple.getItemCode().equalsIgnoreCase(os.getItemCode())) {

							if(lastQty==false) {
								//เพิ่ม record สำหรับ ยอดยกมา
								ITTL_OutStock s = new ITTL_OutStock();
								s.setTimeToRequestReport(timeToRequestReport);							
								//s.setItemCode(((String)tuple[0]).toUpperCase());
								s.setItemCode(tuple.getItemCode().toUpperCase());
								s.setDescription("ยอดยกมา");
								s.setStock(qtyStock);
 
								for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
									if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
										if(lm.getBalanceAmount()==null) {
											s.setStockAmount(0d);
										}else {
											//s.setStockAmount( lm.getBalanceAmount());
											s.setStockAmount( new BigDecimal(lm.getBalanceAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue());
										}
										
										break;
									}
								}
								if(s.getStockAmount()!=null ) {
									//amountStock = s.getStockAmount().doubleValue();
									amountStock = new BigDecimal(s.getStockAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue();
								}else {
									amountStock = 0;
								}
								
								
								if(s.getStock()!=null && s.getStock()!=0) {
									/*double a = (double)Math.round(amountStock/s.getStock()*100000)/100000;									 
									double h = (double)Math.round(a*10000)/10000;									
									s.setPrice(h);*/
									
									
									//BigDecimal c = new BigDecimal(amountStock/s.getStock()).setScale(4, RoundingMode.HALF_UP);
									BigDecimal a = new BigDecimal(""+amountStock);
									BigDecimal b = new BigDecimal(""+s.getStock());
									
									BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
									
									s.setPrice(c.doubleValue());
 									//s.setPrice(amountStock/s.getStock());
								}else{
									s.setPrice(0d);
									s.setStockAmount(0d);
								}
 								//end หาราคาย้อนหลังเดือนที่แล้ว	
								session.save(s);
								//end เพิ่ม record สำหรับ ยอดยกมา
								lastQty = true;
							}
 							if(os.getQtyIN()!=null && os.getQtyIN().doubleValue()>0) {
								qtyStock = qtyStock+os.getQtyIN().doubleValue();
								
								os.setStock(qtyStock);
 								amountStock = amountStock+os.getAmountIN();
								
								os.setStockAmount(amountStock);
							}else {
								double price = 0d;
								if(qtyStock>0) {
									//java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
								  //price = new Double(dfm.format(amountStock/qtyStock)).doubleValue();
									/*double a = (double)Math.round(amountStock/qtyStock*100000)/100000;									 
									double h = (double)Math.round(a*10000)/10000;
									price = h;*/
									BigDecimal a = new BigDecimal(""+amountStock);
									BigDecimal b = new BigDecimal(""+qtyStock);
									
									//BigDecimal c = new BigDecimal(a.doubleValue()/b.doubleValue()).setScale(4, RoundingMode.HALF_UP);
									BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
									price = c.doubleValue();
 								}else {
									price = 0d;
								}
								
								os.setPrice(price);
								
								
								qtyStock = qtyStock-os.getQtyOUT().doubleValue();
								os.setStock(qtyStock);
								if(qtyStock !=0) { 
									os.setAmountOUT(price*os.getQtyOUT().doubleValue());
									//amountStock = amountStock-os.getAmountOUT().doubleValue();
									amountStock = amountStock-new BigDecimal(os.getAmountOUT()).setScale(4, RoundingMode.HALF_UP).doubleValue();
								}else {
									os.setAmountOUT(amountStock);
									amountStock = 0;
								}
								os.setStockAmount(amountStock);
								
							}
							session.save(os);
							
						}
					}					 					 
				}
				 
				if(shelfCode.equals("10")) {
					 
					
					ITTL_MonthlyInventory mi = new ITTL_MonthlyInventory();
					 
					//mi.setItemCode(tuple[0].toString());
					mi.setItemCode(tuple.getItemCode());
					
					if (amountStock>0) {
						mi.setAmount(amountStock);
					}else {					
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
							if(mi.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								if(lm.getBalanceAmount()==null) {
									lm.setBalanceAmount(0d);
								}
								mi.setAmount( lm.getBalanceAmount().doubleValue());
								break;
							}
						}	
					}	
														
					String[] endDate 	  = eDate.split("-");
					Date d = new Date(Integer.parseInt(endDate[0])-1900, Integer.parseInt(endDate[1])-1, Integer.parseInt(endDate[2]));
					mi.setInventoryDate(d);
					 
					
					mi.setMonthly(""+Integer.parseInt(endDate[1]));					 
 					mi.setYearly(endDate[0]);
					 
					session.save(mi);
				}
 				
			}		
			session.getTransaction().commit();
			System.out.println("it's ok");
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(e.getStackTrace().toString());
			return false;
		}finally {
			session.close();
		}
	}
	
	public boolean getMaterialOutStockReport3(String sDate, String eDate, String timeToRequestReport, String shelfCode) {

		Session session = null;
		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
						
			String hql = " SELECT itemCode, sum(qty) " +
				  " FROM VMStockInOut " +
				  " WHERE docDate < '"+sDate+"' "+
				  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
				  "			itemCode like '3-%' or itemCode like '4-%' or " +
				  "			itemCode like '5-%' or itemCode like '6-%' or "+
				  "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+				  
				  " GROUP BY itemCode "+
				  " ORDER BY itemCode ";
			System.out.println(hql);
			List sumItemList = session.createQuery(hql).list();		 	
			System.out.println("sumItemList :"+sumItemList.size());
			
			hql = " FROM VMStockInOut WHERE docDate between '"+sDate+"' and '"+eDate +"' " +
			  	  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
			      "			itemCode like '3-%' or itemCode like '4-%' or " +
			      "			itemCode like '5-%' or itemCode like '6-%' or "+
 			      "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+			
				  " order by itemCode, docDate, qty desc";
			System.out.println(">>> "+hql);
			List<VMStockInOut> result = session.createQuery(hql).list();
			System.out.println("result :"+result.size());	
			
			hql = " FROM ITTL_MonthlyInventory WHERE remark is null and inventoryDate='"+eDate+"' ";
			List<ITTL_MonthlyInventory> invResult = session.createQuery(hql).list();
			int row=0;
			for (ITTL_MonthlyInventory m : invResult) { 
				session.delete(m);
				row++;
			}
			System.out.println("delete ITTL_MonthlyInventory : "+ row+ " record");
	
			// ############# new : start
			String[] startDate 	  = sDate.split("-");
			String hql2 = "FROM ITTL_MonthlyInventoryReport WHERE " ;
			//hql2 = hql2 + " itemCode='7-000-G-003' and 	 ";
			
			if(Integer.parseInt(startDate[1])==1) {
				hql2 +=	" monthReport='12' and yearReport='"+(Integer.parseInt(startDate[0])-1)+"' ";
			}else {
				hql2 +=	" monthReport='"+(Integer.parseInt(startDate[1])-1)+"' " +
						" and yearReport='"+(Integer.parseInt(startDate[0]))+"' ";
			}
			hql2 += " order by itemCode ";
			System.out.println(hql2);
			//list itemCode ของเดือนยอดยกมา
			List<ITTL_MonthlyInventoryReport> lastMonthItemList = session.createQuery(hql2).list();				
			
			// ############# new : end
			
			for (int j = 0; j < lastMonthItemList.size(); j++) {
				
				//Object[] tuple = (Object[])sumItemList.get(j);
				ITTL_MonthlyInventoryReport tuple = (ITTL_MonthlyInventoryReport)lastMonthItemList.get(j);
				
				
				//double qtyStock = ((Double)tuple[1]).doubleValue();
				double qtyStock = tuple.getBalanceQty();
				double amountStock = 0d;
				boolean lastQty = false;
 
				if(result!=null && result.size()==0 && qtyStock!=0) {

					if(lastQty==false) {
						//เพิ่ม record สำหรับ ยอดยกมา
						ITTL_OutStock s = new ITTL_OutStock();
						s.setTimeToRequestReport(timeToRequestReport);							
						//s.setItemCode(((String)tuple[0]).toUpperCase());
						s.setItemCode(tuple.getItemCode().toUpperCase());
						s.setDescription("ยอดยกมา");
						s.setStock(qtyStock);
						//start หาราคาย้อนหลังเดือนที่แล้ว		
 
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {	
							if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								//s.setStockAmount( Double.valueOf(lm.getBalanceAmount()));
								BigDecimal c = new BigDecimal(lm.getBalanceAmount()).setScale(4, RoundingMode.HALF_UP);
								s.setStockAmount(c.doubleValue());
								break;
							}
						}
												
						//amountStock = s.getStockAmount();
						amountStock = new BigDecimal(s.getStockAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue();
						
						if(s.getStock()!=0) {
							//s.setPrice(s.getStockAmount()/s.getStock());
							//s.setPrice(new BigDecimal(s.getStockAmount()/s.getStock()).setScale(4, RoundingMode.HALF_UP).doubleValue());
							
							BigDecimal a = new BigDecimal(""+s.getStockAmount());
							BigDecimal b = new BigDecimal(""+s.getStock());
							
							BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
							s.setPrice(c.doubleValue());
						}else{
							s.setPrice(0d);
							s.setStockAmount(0d);
						}
						
						//end หาราคาย้อนหลังเดือนที่แล้ว	
						session.save(s);
						//end เพิ่ม record สำหรับ ยอดยกมา
						lastQty = true;
					}
										
				}else {
				
					for (int i = 0; i < result.size(); i++) {
 						ITTL_OutStock os = new StockBO().getITTL_OutStock((VMStockInOut)result.get(i));
						
						os.setTimeToRequestReport(timeToRequestReport);
						
						//if(tuple[0].toString().equalsIgnoreCase(os.getItemCode())) {
						if(tuple.getItemCode().equalsIgnoreCase(os.getItemCode())) {

							if(lastQty==false) {
								//เพิ่ม record สำหรับ ยอดยกมา
								ITTL_OutStock s = new ITTL_OutStock();
								s.setTimeToRequestReport(timeToRequestReport);							
								//s.setItemCode(((String)tuple[0]).toUpperCase());
								s.setItemCode(tuple.getItemCode().toUpperCase());
								s.setDescription("ยอดยกมา");
								s.setStock(qtyStock);
 
								for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
									if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
										if(lm.getBalanceAmount()==null) {
											s.setStockAmount(0d);
										}else {
											//s.setStockAmount( lm.getBalanceAmount());
											s.setStockAmount( new BigDecimal(lm.getBalanceAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue());
										}
										
										break;
									}
								}
								if(s.getStockAmount()!=null ) {
									//amountStock = s.getStockAmount().doubleValue();
									amountStock = new BigDecimal(s.getStockAmount()).setScale(4, RoundingMode.HALF_UP).doubleValue();
								}else {
									amountStock = 0;
								}
								
								
								if(s.getStock()!=null && s.getStock()!=0) {
									/*double a = (double)Math.round(amountStock/s.getStock()*100000)/100000;									 
									double h = (double)Math.round(a*10000)/10000;									
									s.setPrice(h);*/
									
									
									//BigDecimal c = new BigDecimal(amountStock/s.getStock()).setScale(4, RoundingMode.HALF_UP);
									BigDecimal a = new BigDecimal(""+amountStock);
									BigDecimal b = new BigDecimal(""+s.getStock());
									
									BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
									
									s.setPrice(c.doubleValue());
 									//s.setPrice(amountStock/s.getStock());
								}else{
									s.setPrice(0d);
									s.setStockAmount(0d);
								}
 								//end หาราคาย้อนหลังเดือนที่แล้ว	
								session.save(s);
								//end เพิ่ม record สำหรับ ยอดยกมา
								lastQty = true;
							}
 							if(os.getQtyIN()!=null && os.getQtyIN().doubleValue()>0) {
								qtyStock = qtyStock+os.getQtyIN().doubleValue();
								
								os.setStock(qtyStock);
 								amountStock = amountStock+os.getAmountIN();
								
								os.setStockAmount(amountStock);
							}else {
								double price = 0d;
								if(qtyStock>0) {
									//java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
								  //price = new Double(dfm.format(amountStock/qtyStock)).doubleValue();
									/*double a = (double)Math.round(amountStock/qtyStock*100000)/100000;									 
									double h = (double)Math.round(a*10000)/10000;
									price = h;*/
									BigDecimal a = new BigDecimal(""+amountStock);
									BigDecimal b = new BigDecimal(""+qtyStock);
									
									//BigDecimal c = new BigDecimal(a.doubleValue()/b.doubleValue()).setScale(4, RoundingMode.HALF_UP);
									BigDecimal c = a.divide(b, 4, RoundingMode.HALF_UP);
									price = c.doubleValue();
 								}else {
									price = 0d;
								}
								
								os.setPrice(price);
								
								
								qtyStock = qtyStock-os.getQtyOUT().doubleValue();
								os.setStock(qtyStock);
								if(qtyStock !=0) { 
									os.setAmountOUT(price*os.getQtyOUT().doubleValue());
									//amountStock = amountStock-os.getAmountOUT().doubleValue();
									amountStock = amountStock-new BigDecimal(os.getAmountOUT()).setScale(4, RoundingMode.HALF_UP).doubleValue();
								}else {
									os.setAmountOUT(amountStock);
									amountStock = 0;
								}
								os.setStockAmount(amountStock);
								
							}
							session.save(os);
							
						}
					}					 					 
				}
				 
				if(shelfCode.equals("10")) {
					 
					
					ITTL_MonthlyInventory mi = new ITTL_MonthlyInventory();
					 
					//mi.setItemCode(tuple[0].toString());
					mi.setItemCode(tuple.getItemCode());
					
					if (amountStock>0) {
						mi.setAmount(amountStock);
					}else {					
						for (ITTL_MonthlyInventoryReport lm : lastMonthItemList) {
							if(mi.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								if(lm.getBalanceAmount()==null) {
									lm.setBalanceAmount(0d);
								}
								mi.setAmount( lm.getBalanceAmount().doubleValue());
								break;
							}
						}	
					}	
														
					String[] endDate 	  = eDate.split("-");
					Date d = new Date(Integer.parseInt(endDate[0])-1900, Integer.parseInt(endDate[1])-1, Integer.parseInt(endDate[2]));
					mi.setInventoryDate(d);
					 
					
					mi.setMonthly(""+Integer.parseInt(endDate[1]));					 
 					mi.setYearly(endDate[0]);
					 
					session.save(mi);
				}
 				
			}		
			session.getTransaction().commit();
			System.out.println("it's ok");
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(e.getStackTrace().toString());
			return false;
		}finally {
			session.close();
		}
	}	
	
	@SuppressWarnings("unchecked")
	public boolean getMaterialOutStockReportOLD(String sDate, String eDate) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
			
			String hql = "DELETE FROM ITTL_OutStock";
			Query query = session.createQuery(hql);
			int row = query.executeUpdate();
			if (row == 0){
				System.out.println("ITTL_OutStock: Doesn' t deleted any row!");
			} else{
				System.out.println("ITTL_OutStock: Deleted Row: " + row);
			}			
			
			hql = " SELECT itemCode, sum(qty) " +
				  " FROM VMStockInOut " +
				  " WHERE docDate < '"+sDate+"' "+
				  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
				  "			itemCode like '3-%' or itemCode like '4-%' or " +
				  "			itemCode like '5-%' or itemCode like '6-%' or "+
				  "  		itemCode like '7-%' ) and whCode ='10' "+
				  " GROUP BY itemCode "+
				  " ORDER BY itemCode ";
//			System.out.println(hql);
			List sumItemList = session.createQuery(hql).list();
			
			
			hql = " FROM VMStockInOut WHERE docDate between '"+sDate+"' and '"+eDate +"' " +
			  	  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
			      "			itemCode like '3-%' or itemCode like '4-%' or " +
			      "			itemCode like '5-%' or itemCode like '6-%' or "+
			      "  		itemCode like '7-%' ) "+			
				  " order by itemCode, docDate, qty desc";
			
			List result = session.createQuery(hql).list();
			
//			System.out.println(hql+"\n"+result.size());
			for (int j = 0; j < sumItemList.size(); j++) {
				Object[] tuple = (Object[])sumItemList.get(j);
				double qtyStock = ((Double)tuple[1]).doubleValue();
				double amountStock = 0d;
				boolean lastQty = false;
				for (int i = 0; i < result.size(); i++) {
					
					ITTL_OutStock os = new StockBO().getITTL_OutStock((VMStockInOut)result.get(i));
					if(tuple[0].equals(os.getItemCode())) {
						if(lastQty==false) {
							//เพิ่ม record สำหรับ ยอดยกมา
							ITTL_OutStock s = new ITTL_OutStock();
							s.setItemCode((String)tuple[0]);
							s.setDescription("ยอดยกมา");
							s.setStock(qtyStock);
							//start หาราคาย้อนหลังเดือนที่แล้ว							
							String hql2 = "FROM ITTL_MonthlyInventory " +
											" WHERE itemCode='"+s.getItemCode()+"' " +
											" 		and month(inventoryDate)='6'";
							List itemList = session.createQuery(hql2).list();
							ITTL_MonthlyInventory tupleAmount = (ITTL_MonthlyInventory)itemList.get(0);
							
							s.setStockAmount( tupleAmount.getAmount());
							amountStock = s.getStockAmount();
							
							if(s.getStock()>0) {
								s.setPrice(s.getStockAmount()/s.getStock());
							}else{
								s.setPrice(0d);
							}
							
							//end หาราคาย้อนหลังเดือนที่แล้ว	
							session.save(s);
							//end เพิ่ม record สำหรับ ยอดยกมา
							lastQty = true;
						}
						//System.out.print(tuple[0]+"\n");
						if(os.getQtyIN().doubleValue()>0) {
							qtyStock = qtyStock+os.getQtyIN().doubleValue();
							
							os.setStock(qtyStock);
							amountStock = amountStock+os.getAmountIN();
							
							os.setStockAmount(amountStock);
						}else {
							double price = 0d;
							if(qtyStock>0) {
								java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
								price = new Double(dfm.format(amountStock/qtyStock)).doubleValue();
								
								//price = (amountStock/qtyStock);
							}else {
								price = 0d;
							}
							
							os.setPrice(price);
							os.setAmountOUT(price*os.getQtyOUT().doubleValue());
							
							qtyStock = qtyStock-os.getQtyOUT().doubleValue();
							os.setStock(qtyStock);
							
							amountStock = amountStock-os.getAmountOUT().doubleValue();
							os.setStockAmount(amountStock);
							
						}
						//System.out.println(os.getQtyIN().doubleValue()>=0);
						session.save(os);
					}	
				}
			}
			
			session.getTransaction().commit();
			
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings({ "unchecked", "deprecation", "rawtypes" })
	public boolean getMaterialOutStockReport2(String sDate, String eDate, String timeToRequestReport, String shelfCode) {
		Session session = null;
		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
						
			String hql = " SELECT itemCode, sum(qty) " +
				  " FROM VMStockInOut " +
				  " WHERE docDate < '"+sDate+"' "+
				  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
				  "			itemCode like '3-%' or itemCode like '4-%' or " +
				  "			itemCode like '5-%' or itemCode like '6-%' or "+
				  "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+				  
				  " GROUP BY itemCode "+
				  " ORDER BY itemCode ";
			System.out.println(hql);
			List sumItemList = session.createQuery(hql).list();		 	
			System.out.println("sumItemList :"+sumItemList.size());
			
			hql = " FROM VMStockInOut WHERE docDate between '"+sDate+"' and '"+eDate +"' " +
			  	  "    and (itemCode like '1-%' or itemCode like '2-%' or " +
			      "			itemCode like '3-%' or itemCode like '4-%' or " +
			      "			itemCode like '5-%' or itemCode like '6-%' or "+
//			      "  		itemCode like '7-%' ) "+
			      "  		itemCode like '7-%' ) and whCode ='10' and shelfCode='"+shelfCode+"' "+			
				  " order by itemCode, docDate, qty desc";
			System.out.println(">>> "+hql);
			List<VMStockInOut> result = session.createQuery(hql).list();
			System.out.println("result :"+result.size());	
 
			
			hql = " FROM ITTL_MonthlyInventory WHERE remark is null and inventoryDate='"+eDate+"' ";
			List<ITTL_MonthlyInventory> invResult = session.createQuery(hql).list();
			int row=0;
			for (ITTL_MonthlyInventory m : invResult) { 
				session.delete(m);
				row++;
			}
			System.out.println("delete ITTL_MonthlyInventory : "+ row+ " record");

			String[] startDate 	  = sDate.split("-");
			String hql2 = "FROM ITTL_MonthlyInventory WHERE  " ;
							
			if(Integer.parseInt(startDate[1])==1) {
				hql2 +=	" month(inventoryDate)='12' and year(inventoryDate)='"+(Integer.parseInt(startDate[0])-1)+"' ";
			}else {
				hql2 +=	" month(inventoryDate)='"+(Integer.parseInt(startDate[1])-1)+"' " +
						" and year(inventoryDate)='"+(Integer.parseInt(startDate[0]))+"' ";
			}
			System.out.println(hql2);
			//list itemCode ของเดือนยอดยกมา
			List<ITTL_MonthlyInventory> lastMonthItemList = session.createQuery(hql2).list();			
			
			for (int j = 0; j < sumItemList.size(); j++) {
				
				Object[] tuple = (Object[])sumItemList.get(j);
				
				double qtyStock = ((Double)tuple[1]).doubleValue();
				double amountStock = 0d;
				boolean lastQty = false;
 
				if(result!=null && result.size()==0 && qtyStock!=0) {

					if(lastQty==false) {
						//เพิ่ม record สำหรับ ยอดยกมา
						ITTL_OutStock s = new ITTL_OutStock();
						s.setTimeToRequestReport(timeToRequestReport);							
						s.setItemCode(((String)tuple[0]).toUpperCase());
						s.setDescription("ยอดยกมา");
						s.setStock(qtyStock);
						 
						for (ITTL_MonthlyInventory lm : lastMonthItemList) {
							if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								s.setStockAmount( lm.getAmount());
								break;
							}
						}
						
						amountStock = s.getStockAmount();
						
						if(s.getStock()!=0) {
							s.setPrice(s.getStockAmount()/s.getStock());
						}else{
							s.setPrice(0d);
							s.setStockAmount(0d);
						}
						//end หาราคาย้อนหลังเดือนที่แล้ว	
						session.save(s);
						//end เพิ่ม record สำหรับ ยอดยกมา
						lastQty = true;
					}
										
				}else {
				
					for (int i = 0; i < result.size(); i++) {

						ITTL_OutStock os = new StockBO().getITTL_OutStock((VMStockInOut)result.get(i));
						
						os.setTimeToRequestReport(timeToRequestReport);
 
						
						if(tuple[0].toString().equalsIgnoreCase(os.getItemCode())) {
 
							if(lastQty==false) {
								//เพิ่ม record สำหรับ ยอดยกมา
								ITTL_OutStock s = new ITTL_OutStock();
								s.setTimeToRequestReport(timeToRequestReport);							
								s.setItemCode(((String)tuple[0]).toUpperCase());
								s.setDescription("ยอดยกมา");
								s.setStock(qtyStock);
 
								for (ITTL_MonthlyInventory lm : lastMonthItemList) {
									if(s.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
										if(lm.getAmount()==null) {
											s.setStockAmount(0d);
										}else {
											s.setStockAmount( lm.getAmount());	
										}
										break;
									}
								}
								if(s.getStockAmount()!=null ) {
									amountStock = s.getStockAmount();
								}else {
									amountStock = 0;
								}
								
								if(s.getStock()!=null && s.getStock()!=0) {
									s.setPrice(amountStock/s.getStock());
								}else{
									s.setPrice(0d);
									s.setStockAmount(0d);
								}
								//end หาราคาย้อนหลังเดือนที่แล้ว	
								session.save(s);
								//end เพิ่ม record สำหรับ ยอดยกมา
								lastQty = true;
							}
							if(os.getQtyIN()!=null && os.getQtyIN().doubleValue()>0) {
								qtyStock = qtyStock+os.getQtyIN().doubleValue();
								
								os.setStock(qtyStock);
								amountStock = amountStock+os.getAmountIN();
								
								os.setStockAmount(amountStock);
							}else {
								double price = 0d;
								if(qtyStock>0) {
									java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
									price = new Double(dfm.format(amountStock/qtyStock)).doubleValue();
								}else {
									price = 0d;
								}
								
								os.setPrice(price);
								
								qtyStock = qtyStock-os.getQtyOUT().doubleValue();
								os.setStock(qtyStock);
								if(qtyStock !=0) { 
									os.setAmountOUT(price*os.getQtyOUT().doubleValue());
									amountStock = amountStock-os.getAmountOUT().doubleValue();
								}else {
									os.setAmountOUT(amountStock);
									amountStock = 0;
								}
								os.setStockAmount(amountStock);
								
							}
							session.save(os);
						}
					}
				}
				 
				if(shelfCode.equals("10")) {
					 
					ITTL_MonthlyInventory mi = new ITTL_MonthlyInventory();
					 
					mi.setItemCode(tuple[0].toString());
					
					if (amountStock>0) {
						mi.setAmount(amountStock);
					}else {					
						for (ITTL_MonthlyInventory lm : lastMonthItemList) {
							if(mi.getItemCode().equalsIgnoreCase(lm.getItemCode())) {
								if(lm.getAmount()==null) {
									lm.setAmount(0d);
								}
								mi.setAmount( lm.getAmount().doubleValue());
								break;
							}
						}	
					}	
					
					String[] endDate 	  = eDate.split("-");
					Date d = new Date(Integer.parseInt(endDate[0])-1900, Integer.parseInt(endDate[1])-1, Integer.parseInt(endDate[2]));
					mi.setInventoryDate(d);
					mi.setMonthly(""+Integer.parseInt(endDate[1]));
 					mi.setYearly(endDate[0]);
					 
					session.save(mi);
				}
 				
			}		
			session.getTransaction().commit();
			System.out.println("it's ok");
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(e.getStackTrace().toString());
			return false;
		}finally {
			session.close();
		}		
	}

	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean monthlyClosed(int closedStatus, int yearReport, int monthReport) {
		Session session = null;
		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
				
			String hql = "From ITTL_MonthlyClosed" +
					" WHERE yearReport="+yearReport+
					" and monthReport="+monthReport;					
			
			List<ITTL_MonthlyClosed> mcList = session.createQuery(hql).list();	
			
			if(mcList!=null && !mcList.isEmpty()) {
				ITTL_MonthlyClosed mc = (ITTL_MonthlyClosed)mcList.get(0); 														
				mc.setClosedStatus(closedStatus);					
				session.save(mc);				
			}
			
			int nextMonth = 0;
			int nextYear = 0;
			
			if(monthReport==12) {
				nextMonth=1;
				nextYear=yearReport+1;
			}else {
				nextMonth=monthReport+1;
				nextYear= yearReport;
			}
			
			if(closedStatus==1) {
											
				hql = "From ITTL_MonthlyClosed" +
						" WHERE yearReport="+nextYear+
						" and monthReport="+nextMonth;
				System.out.println(hql);
				mcList = session.createQuery(hql).list();	
				
				if(mcList==null || mcList.isEmpty()) {
					ITTL_MonthlyClosed mc = new ITTL_MonthlyClosed();
					mc.setYearReport(nextYear);
					mc.setMonthReport(nextMonth);
					mc.setClosedStatus(0);					
					session.save(mc);
				}
								
			}else {//closedStatus=0
				hql = " From ITTL_MonthlyClosed" +
						" WHERE yearReport=:nextYear"+
						" and  monthReport=:nextMonth";
				Query q = session.createQuery(hql);
				q.setParameter("nextYear", nextYear);
				q.setParameter("nextMonth", nextMonth);
				
				List<ITTL_MonthlyClosed> list = q.list();
				
				if(list!=null && !list.isEmpty()) {
					for (ITTL_MonthlyClosed m : list) {
						session.delete(m);
					}
				}
				
				
			}
			
			session.getTransaction().commit();
			System.out.println("add it's ok");
			return true;
			
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(e.getStackTrace().toString());
			return false;
		}finally {
			session.close();
		}
	}
	 	
}
